﻿using Dapper;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

namespace ARS.Common.Helper
{
    public class PgSqlHelper
    {
        private string Connection;
        public PgSqlHelper(string connection)
        {
            Init(connection);
        }
        public void Init(string connection = null)
        {
           
                Connection = connection;

        }
        private NpgsqlConnection getConnection()
        {
            return new NpgsqlConnection(Connection);
        }

        /// <summary>
        /// DB Connection 已打开
        /// </summary>
        public IDbConnection PgConnection
        {
            get
            {
                return getConnection();
            }
        }

        public Tuple<IEnumerable<T>, int> GetListByPage<T>(string tableName, string fields, string whereFragment, string orderbyFragment, int pageIndex = 1, int pageSize = 50)
        {

            if (string.IsNullOrEmpty(tableName))
            {
                throw new System.Exception("无表名");
            }
            if (string.IsNullOrEmpty(orderbyFragment))
            {
                throw new System.Exception("无排序字段");
            }
            if (string.IsNullOrEmpty(fields))
            {
                fields = "*";
            }
            var whereSql = "";
            if (!string.IsNullOrEmpty(whereFragment))
            {
                whereSql = $"where {whereFragment}";
            }
            if (pageIndex <= 0)
                pageIndex = 1;
            var sql = $@"with tab as
(
select {fields},ROW_NUMBER() OVER (ORDER BY {orderbyFragment} ) AS RowNumber from {tableName} {whereSql})
select {fields} from  tab where RowNumber>{(pageIndex - 1) * pageSize} and RowNumber<={pageIndex * pageSize};
";
            var sqltotal = $@"select count(1) cts from {tableName} {whereSql};";


            using (IDbConnection conn = PgConnection)
            {
                var res = conn.Query<T>(sql);
                var rowCounts = conn.QuerySingle<int>(sqltotal);
                conn.Close();

                return Tuple.Create(res, rowCounts);
            }
        }

    }
}
